﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SqlOrm.Xml;
using System.IO;
using System.Reflection;

namespace SqlOrm
{
    public class OrmBuilder
    {
        readonly DataDictionary _ddl;
        public OrmBuilder(DataDictionary ddl)
        {
            _ddl = ddl;
        }

        public void BuildSqlContext()
        {
            int d = 0;
            #region Templates
            //string contextTemplate = Assembly.GetExecutingAssembly().GetResourceTemplate("SqlContext.template");

            //contextTemplate = contextTemplate.Replace("%Header%", "/*   This file was generated by some tool */");
            //contextTemplate = contextTemplate.Replace("%Namespace%", _ddl.Settings.ContextOutput.clrnamespace);

            //contextTemplate = contextTemplate.Replace("%ContextUpserts%", BuildUpserts());
            //contextTemplate = contextTemplate.Replace("%ContextGets%", BuildGets());
            //contextTemplate = contextTemplate.Replace("%ContextSearches%", BuildSearches());
            //contextTemplate = contextTemplate.Replace("%ContextAlls%", BuildAlls());
            //contextTemplate = contextTemplate.Replace("%ContextDeletes%", BuildDeletes());
            #endregion

            string tmpfile = Path.GetTempFileName();
            using (var sw = new StreamWriter(tmpfile))
            {
                sw.WriteLine("/*");
                sw.WriteLine("This file was generated by some tool.");
                sw.WriteLine("SqlData.exe");
                sw.WriteLine("*/");
                sw.WriteLine(@"using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;");
                if (_ddl.Settings.ContextOutput.clrnamespace != _ddl.Settings.EntitiesOutput.clrnamespace)
                    sw.WriteLine(Indent(d) + "using " + _ddl.Settings.EntitiesOutput.clrnamespace);
                sw.WriteLine();
                sw.WriteLine(string.Format("namespace {0}", _ddl.Settings.ContextOutput.clrnamespace));
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "public enum EntityActivity { Active, Inactive, ActiveInactive };");
                sw.WriteLine(string.Format(Indent(d) + "public class {0}", "SqlContext: IDisposable"));
                sw.WriteLine(Indent(d++) + "{");
                //sw.WriteLine(Indent(d) + "readonly string _connectionString;");
                sw.WriteLine(Indent(d) + "readonly object _syncRoot = new object();");
                sw.WriteLine(Indent(d) + "SqlConnection _conn;");
                sw.WriteLine(Indent(d) + "SqlTransaction _transaction;");
                sw.WriteLine(Indent(d) + "bool _transactionActive;");
                sw.WriteLine(Indent(d) + "bool _keepAlive;");
                sw.WriteLine();
                sw.WriteLine(Indent(d) + "public SqlContext(string connectionString)");
                sw.WriteLine(Indent(d++) + "{");
                //sw.WriteLine(Indent(d) + "_connectionString = connectionString;");
                sw.WriteLine(Indent(d) + "_conn = new SqlConnection(connectionString);");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine();
                sw.WriteLine(Indent(d) + "public bool KeepAlive");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "get { return _keepAlive; }");
                sw.WriteLine(Indent(d) + "set { _keepAlive = value; }");
                sw.WriteLine(Indent(--d) + "}");

                sw.WriteLine(Indent(d) + "#region ExecuteSql");
                BuildExecuteSqlMethod(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Transactions");
                BuildTransactions(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Dispose");
                BuildDisposeMethod(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Upserts");
                BuildUpserts(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Gets");
                BuildGets(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Searches");
                BuildSearches(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region All");
                BuildAlls(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Procs");
                BuildProcs(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");
                sw.WriteLine(Indent(d) + "#region Deletes");
                BuildDeletes(sw, d);
                sw.WriteLine(Indent(d) + "#endregion");

                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.Flush();
            }
            Move(_ddl.Settings.ContextOutput.pathname, tmpfile);
        }

        private void BuildTransactions(StreamWriter sw, int d)
        {
            sw.WriteLine(Indent(d) + "public bool BeginTransaction()");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "if (!_transactionActive)");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "_keepAlive = true;");
            sw.WriteLine(Indent(d) + "_transactionActive = true;");
            sw.WriteLine(Indent(d++) + "if (_conn.State != ConnectionState.Open)");
            sw.WriteLine(Indent(d--) + "_conn.Open();");
            sw.WriteLine(Indent(d) + "_transaction = _conn.BeginTransaction();");
            sw.WriteLine(Indent(d) + "return true;");
            sw.WriteLine(Indent(--d) + "}");
            sw.WriteLine(Indent(d) + "return false;");
            sw.WriteLine(Indent(--d) + "}");

            sw.WriteLine(Indent(d) + "public void CommitTransaction()");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "if (_transactionActive)");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "_transaction.Commit();");
            sw.WriteLine(Indent(d) + "_transactionActive = false;");
            sw.WriteLine(Indent(d) + "_transaction.Dispose();");
            sw.WriteLine(Indent(--d) + "}");
            sw.WriteLine(Indent(--d) + "}");

            sw.WriteLine(Indent(d) + "public void RollbackTransaction()");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "if (_transactionActive)");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "_transaction.Rollback();");
            sw.WriteLine(Indent(d) + "_transactionActive = false;");
            sw.WriteLine(Indent(d) + "_transaction.Dispose();");
            sw.WriteLine(Indent(--d) + "}");
            sw.WriteLine(Indent(--d) + "}");

        }

        private void BuildAlls(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.all))
            {
                string methodName = string.Format("All{0}", table.name);
                bool rowActivity = !string.IsNullOrEmpty(table.activefield) && table.Field.Select(x => x.name).Contains(table.activefield);
                sw.WriteLine(Indent(d) + string.Format("public List<{0}> {1}()", table.name, methodName));

                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "lock (_syncRoot)");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "var dt = new DataTable(\"{0}\");", methodName);
                sw.WriteLine(Indent(d) + string.Format("var result = new List<{0}>();", table.name));
                MaybeOpenConnection(sw, d);

                sw.WriteLine();

                sw.WriteLine(Indent(d) + string.Format("using (var command = new SqlCommand(\"{0}\", _conn))", methodName));
                sw.WriteLine(Indent(d++) + "{");

                sw.WriteLine(Indent(d) + "command.CommandType = CommandType.StoredProcedure;");
                sw.WriteLine(Indent(d++) + "if (_transactionActive)");
                sw.WriteLine(Indent(d--) + "command.Transaction = _transaction;");
                sw.WriteLine();

                sw.WriteLine(Indent(d) + "using(var reader = command.ExecuteReader())");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "while (reader.Read())");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "var item = new {0}();", table.name);

                SqlCastReturnedFields(table.Field.ToList(),false, sw, d);

                sw.WriteLine(Indent(d) + "result.Add(item);", table.name);
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");

                MaybeCloseConnection(sw, d);

                sw.WriteLine(Indent(d) + "return result;");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine();

            }
        }

        private string BuildAlls()
        {
            var result = new StringBuilder();
            string allTemplate = Assembly.GetExecutingAssembly().GetResourceTemplate("ContextAll.template");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.all))
            {
                string all = allTemplate.Replace("%TableName%", table.name);
                string methodName = string.Format("All{0}", table.name);
                all = all.Replace("%MethodName%", methodName);
                bool rowActivity = !string.IsNullOrEmpty(table.activefield) && table.Field.Select(x => x.name).Contains(table.activefield);

                string cmdto = _ddl.Settings.CommandTimeoutSpecified ? string.Format(Indent(5) + "command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout) : null;
                all = all.Replace("%CommandTimeout%", cmdto);

                all = all.Replace("%CastedReturnSetEntityProperties%", SqlCastReturnedFields(table.Field.ToList(), false, 7));

                result.AppendLine(all);
            }
            return result.ToString();
        }

        private void BuildProcs(StreamWriter sw, int d)
        {
            foreach (var proc in _ddl.Definition.Procedures)
            {
                bool hasOutput = (proc.Output != null && proc.UseFields.Count > 0);
                string returnDataType = proc.name + "Output";
                string returnType = (hasOutput) ? string.Format("List<{0}>", returnDataType) : "void";
                sw.Write(Indent(d) + string.Format("public {0} {1}(", returnType, proc.name));
                if (proc.Input != null && proc.Input.Length > 0)
                {
                    foreach (var f in proc.Input)
                    {
                        sw.Write("{0} {1}", f.ClrDataType, f.name);
                        if (Array.IndexOf(proc.Input, f) != proc.Input.Count() - 1)
                            sw.Write(",");
                    }
                }
                sw.WriteLine(")");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "lock (_syncRoot)");
                sw.WriteLine(Indent(d++) + "{");
                if (hasOutput)
                {
                    sw.WriteLine(Indent(d) + "var dt = new DataTable(\"{0}\");", proc.name);
                    sw.WriteLine(Indent(d) + string.Format("var result = new {0}();", returnType));
                }
                MaybeOpenConnection(sw, d);

                sw.WriteLine();

                sw.WriteLine(Indent(d) + string.Format("using (var command = new SqlCommand(\"{0}\", _conn))", proc.name));
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + string.Format("command.CommandType = CommandType.StoredProcedure;"));
                sw.WriteLine(Indent(d++) + "if (_transactionActive)");
                sw.WriteLine(Indent(d--) + "command.Transaction = _transaction;");
                sw.WriteLine();

                if (proc.Input!=null && proc.Input.Length>0)
                    SqlParameters(proc.Input.ToList(), true, sw, d);

                if (hasOutput)
                {

                    sw.WriteLine(Indent(d) + "using(var reader = command.ExecuteReader())");
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(Indent(d) + "while (reader.Read())");
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(Indent(d) + "var item = new {0}();", returnDataType);
                    foreach (var field in proc.UseFields)
                    {
                        sw.WriteLine(Indent(d) + string.Format("item.{0} = ({1})reader[\"{2}\"];", field.title, field.ClrDataType, field.name));
                    }
                    sw.WriteLine(Indent(d) + "result.Add(item);");
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine(Indent(--d) + "}");
                }
                else
                    sw.WriteLine(Indent(d) + "command.ExecuteNonQuery();");

                sw.WriteLine(Indent(--d) + "}");
                MaybeCloseConnection(sw, d);
                if (hasOutput) sw.WriteLine(Indent(d) + "return result;");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine();
            }
        }

        private void BuildSearches(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => !string.IsNullOrEmpty(x.search)))
            {
                string[] patterns = table.search.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var pattern in patterns)
                {
                    string[] sname = pattern.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    List<Field> searchFields = new List<Field>();
                    foreach (var s in sname)
                    {
                        Field f = table.Field.Where(x => x.name == s).FirstOrDefault();
                        if (f != null)
                            searchFields.Add(f);
                    }
                    string procArgsName = string.Empty;
                    Array.ForEach(searchFields.ToArray(), x => procArgsName += x.title);
                    string methodName = string.Format("Search{0}By{1}", table.name, procArgsName);
                    sw.Write(Indent(d) + string.Format("public List<{0}> {1}(", table.name, methodName));
                    foreach (var f in searchFields)
                    {
                        //if (!string.IsNullOrEmpty(f.EnumName))
                        //    sw.Write("{0} {1}", f.EnumName, f.name);
                        //else
                            sw.Write("{0} {1}", f.ClrDataType, f.name);
                        if (Array.IndexOf(searchFields.ToArray(), f) != searchFields.Count() - 1)
                            sw.Write(",");
                    }
                    sw.WriteLine(")");
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(Indent(d) + "lock (_syncRoot)");
                    sw.WriteLine(Indent(d++) + "{");
                    //sw.WriteLine(Indent(d) + "var dt = new DataTable(\"{0}\");", methodName);
                    sw.WriteLine(Indent(d) + string.Format("var result = new List<{0}>();", table.name));
                    MaybeOpenConnection(sw, d);

                    sw.WriteLine();

                    sw.WriteLine(Indent(d) + string.Format("using (var command = new SqlCommand(\"{0}\", _conn))", methodName));
                    sw.WriteLine(Indent(d++) + "{");
                    if (_ddl.Settings.CommandTimeoutSpecified)
                        sw.WriteLine(Indent(d) + string.Format("command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout));
                    sw.WriteLine(Indent(d) + string.Format("command.CommandType = CommandType.StoredProcedure;"));
                    sw.WriteLine(Indent(d++) + "if (_transactionActive)");
                    sw.WriteLine(Indent(d--) + "command.Transaction = _transaction;");
                    sw.WriteLine();

                    SqlParameters(searchFields, true, sw, d);

                    sw.WriteLine(Indent(d) + "using(var reader = command.ExecuteReader())");
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(Indent(d) + "while (reader.Read())");
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(Indent(d) + "var item = new {0}();", table.name);

                    SqlCastReturnedFields(table.Field.ToList(), false, sw, d);

                    sw.WriteLine(Indent(d) + "result.Add(item);");
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine(Indent(--d) + "}");
                    MaybeCloseConnection(sw, d);
                    sw.WriteLine(Indent(d) + "return result;");
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine();
                }
            }
        }

        private string BuildSearches()
        {
            var result = new StringBuilder();
            string searchesTemplate = Assembly.GetExecutingAssembly().GetResourceTemplate("ContextSearch.template");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => !string.IsNullOrEmpty(x.search)))
            {
                string[] patterns = table.search.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var pattern in patterns)
                {
                    string search = searchesTemplate.Replace("%TableName%", table.name);

                    string[] sname = pattern.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    var searchFields = new List<Field>();
                    foreach (var s in sname)
                    {
                        Field f = table.Field.Where(x => x.name == s).FirstOrDefault();
                        if (f != null)
                            searchFields.Add(f);
                    }
                    string procArgsName = string.Empty;
                    Array.ForEach(searchFields.ToArray(), x => procArgsName += x.title);
                    string methodName = string.Format("Search{0}By{1}", table.name, procArgsName);

                    search = search.Replace("%MethodName%", methodName);
                    
                    var searchParams = new StringBuilder();
                    foreach (var f in searchFields)
                    {
                        searchParams.AppendFormat("{0} {1}", f.ClrDataType, f.name);
                        if (Array.IndexOf(searchFields.ToArray(), f) != searchFields.Count() - 1)
                            searchParams.Append(",");
                    }

                    search = search.Replace("%MethodParameters%", searchParams.ToString());

                    string cmdto = _ddl.Settings.CommandTimeoutSpecified ? string.Format( Indent(5) + "command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout) : null;
                    search = search.Replace("%CommandTimeout%", cmdto);

                    search = search.Replace("%Parameters%", SqlParameters(searchFields, true, 5));

                    search = search.Replace("%CastedReturnSetEntityProperties%", SqlCastReturnedFields(table.Field.ToList(), false, 7));
                    result.AppendLine(search);
                }
            }
            return result.ToString();
        }

        private void BuildDeletes(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table.Where(x=>x.delete))
            {
                sw.Write(Indent(d) + string.Format("public void Delete{0}(", table.name));
                foreach (var f in table.Field.Where(x => x.key))
                {
                    sw.Write("{0} {1}", f.ClrDataType, f.name);
                    if (Array.IndexOf(table.Field, f) != table.Field.Where(x => x.key).Count() - 1)
                        sw.Write(",");
                }
                sw.WriteLine(")");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "lock (_syncRoot)");
                sw.WriteLine(Indent(d++) + "{");
                //sw.WriteLine(Indent(d) + "var dt = new DataTable(\"{0}\");", "Get" + table.name);
                //sw.WriteLine(Indent(d) + string.Format("{0} result = null;", table.name));
                MaybeOpenConnection(sw, d);

                sw.WriteLine();

                sw.WriteLine(Indent(d) + string.Format("using (var command = new SqlCommand(\"{0}\", _conn))", "Delete" + table.name));
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + string.Format("command.CommandType = CommandType.StoredProcedure;"));
                sw.WriteLine(Indent(d++) + "if (_transactionActive)");
                sw.WriteLine(Indent(d--) + "command.Transaction = _transaction;");
                sw.WriteLine();

                SqlParameters(table.Field.Where(x => x.key).ToList(), true, sw, d);
                sw.WriteLine(Indent(d) + "command.ExecuteNonQuery();");
                //sw.WriteLine(Indent(d) + "using(var reader = command.ExecuteReader(CommandBehavior.SingleRow))");
                //sw.WriteLine(Indent(d++) + "{");
                //sw.WriteLine(Indent(d) + "while (reader.Read())");
                //sw.WriteLine(Indent(d++) + "{");
                //sw.WriteLine(Indent(d) + string.Format("result = new {0}();", table.name));

                //SqlCastReturnedFields(table.Field.ToList(), true, sw, d);

                //sw.WriteLine(Indent(--d) + "}");
                //sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                MaybeCloseConnection(sw, d);
                //sw.WriteLine(Indent(d) + "return result;");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine();
            }
        }

        private string BuildDeletes()
        {
            var result = new StringBuilder();
            string deleteTemplate = Assembly.GetExecutingAssembly().GetResourceTemplate("ContextDelete.template");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.delete))
            {
                string delete = deleteTemplate.Replace("%TableName%", table.name);
                //sw.Write(Indent(d) + string.Format("public void Delete{0}(", table.name));

                var methodParams = new StringBuilder();
                foreach (var f in table.Field.Where(x => x.key))
                {
                    methodParams.AppendFormat("{0} {1}", f.ClrDataType, f.name);
                    if (Array.IndexOf(table.Field, f) != table.Field.Where(x => x.key).Count() - 1)
                        methodParams.Append(",");
                }

                delete = delete.Replace("%MethodParameters%", methodParams.ToString());

                string cmdto = _ddl.Settings.CommandTimeoutSpecified ? string.Format(Indent(5) + "command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout) : null;
                delete = delete.Replace("%CommandTimeout%", cmdto);

                delete = delete.Replace("%Parameters%", SqlParameters(table.Field.Where(x => x.key).ToList(), true, 5));
                result.Append(delete);
            }
            return result.ToString();
        }

        private void BuildGets(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                sw.Write(Indent(d) + string.Format("public {0} Get{0}(", table.name));
                foreach (var f in table.Field.Where(x => x.key))
                {
                    sw.Write("{0} {1}", f.ClrDataType, f.name);
                    if (Array.IndexOf(table.Field, f) != table.Field.Where(x => x.key).Count() - 1)
                        sw.Write(",");
                }
                sw.WriteLine(")");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "lock (_syncRoot)");
                sw.WriteLine(Indent(d++) + "{");
                //sw.WriteLine(Indent(d) + "var dt = new DataTable(\"{0}\");", "Get" + table.name);
                sw.WriteLine(Indent(d) + string.Format("{0} result = null;", table.name));
                MaybeOpenConnection(sw, d);

                sw.WriteLine();

                sw.WriteLine(Indent(d) + string.Format("using (var command = new SqlCommand(\"{0}\", _conn))", "Get" + table.name));
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + string.Format("command.CommandType = CommandType.StoredProcedure;"));
                sw.WriteLine(Indent(d++) + "if (_transactionActive)");
                sw.WriteLine(Indent(d--) + "command.Transaction = _transaction;");
                sw.WriteLine();

                SqlParameters(table.Field.Where(x => x.key).ToList(), true, sw, d);

                sw.WriteLine(Indent(d) + "using(var reader = command.ExecuteReader(CommandBehavior.SingleRow))");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "while (reader.Read())");
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + string.Format("result = new {0}();", table.name));

                SqlCastReturnedFields(table.Field,true, sw, d);

                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                MaybeCloseConnection(sw, d);
                sw.WriteLine(Indent(d) + "return result;");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine();
            }
        }

        private string BuildGets()
        {
            var result = new StringBuilder();
            string getTemplate = Assembly.GetExecutingAssembly().GetResourceTemplate("ContextGet.template");
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                string get = getTemplate.Replace("%TableName%", table.name);
                var methodParameters = new StringBuilder();
                foreach (var f in table.Field.Where(x => x.key))
                {
                    methodParameters.AppendFormat("{0} {1}", f.ClrDataType, f.name);
                    if (Array.IndexOf(table.Field, f) != table.Field.Where(x => x.key).Count() - 1)
                        methodParameters.Append(",");
                }
                string cmdto = _ddl.Settings.CommandTimeoutSpecified ? string.Format(Indent(5) + "command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout) : null;
                get = get.Replace("%CommandTimeout%", cmdto);

                get = get.Replace("%MethodParameters%", methodParameters.ToString());

                string param = SqlParameters(table.Field.Where(x => x.key).ToList(), true, 5);
                get = get.Replace("%Parameters%", param);

                string castedReturn = SqlCastReturnedFields(table.Field, true,  7);
                get = get.Replace("%CastedReturnSetEntityProperties%", castedReturn);
                result.AppendLine(get);
            }
            return result.ToString();
        }

        private void SqlCastReturnedFields(IEnumerable<Field> fields, bool singleRow, StreamWriter sw, int d)
        {
            string item = (singleRow) ? "result" : "item";
            foreach (var field in fields)
            {
                string test = string.Empty;
                string notnull;
                if (!string.IsNullOrEmpty(field.EnumName))
                    notnull = string.Format("({0})({1})reader[\"{2}\"]", field.ClrDataType, field.UnderlyingEnumType, field.name);
                else
                    notnull = string.Format("({0})reader[\"{1}\"]", field.ClrDataType, field.name);
                string isnull = string.Empty;
                if (field.nulls)
                {
                    test = string.Format("(reader[\"{0}\"] != DBNull.Value)?", field.name);
                    isnull = ":null";
                }
                sw.WriteLine(Indent(d) + string.Format("{4}.{0} = {1}{2}{3};", field.title, test, notnull, isnull, item));
            }
        }

        private string SqlCastReturnedFields(IEnumerable<Field> fields, bool singleRow, int d)
        {
            var result = new StringBuilder();
            string item = (singleRow) ? "result" : "item";
            foreach (var field in fields)
            {
                string test = string.Empty;
                string notnull;
                if (!string.IsNullOrEmpty(field.EnumName))
                    notnull = string.Format("({0})({1})reader[\"{2}\"]", field.ClrDataType, field.UnderlyingEnumType, field.name);
                else
                    notnull = string.Format("({0})reader[\"{1}\"]", field.ClrDataType, field.name);
                string isnull = string.Empty;
                if (field.nulls)
                {
                    test = string.Format("(reader[\"{0}\"] != DBNull.Value)?", field.name);
                    isnull = ":null";
                }
                result.AppendLine(Indent(d) + string.Format("{4}.{0} = {1}{2}{3};", field.title, test, notnull, isnull, item));
            }
            return result.ToString();
        }

        private void BuildUpserts(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                sw.WriteLine(Indent(d) + string.Format("public void Upsert{0}({0} det)", table.name));
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + "lock (_syncRoot)");
                sw.WriteLine(Indent(d++) + "{");

                MaybeOpenConnection(sw, d);

                sw.WriteLine();

                sw.WriteLine(Indent(d) + string.Format("using (var command = new SqlCommand(\"{0}\", _conn))", "Upsert" + table.name));
                sw.WriteLine(Indent(d++) + "{");
                sw.WriteLine(Indent(d) + string.Format("command.CommandType = CommandType.StoredProcedure;"));
                sw.WriteLine(Indent(d++) + "if (_transactionActive)");
                sw.WriteLine(Indent(d--) + "command.Transaction = _transaction;");
                sw.WriteLine();

                SqlParameters(table.Field.ToList(),false, sw, d);

                sw.WriteLine(Indent(d) + "command.ExecuteNonQuery();");
                sw.WriteLine(Indent(--d) + "}");
                MaybeCloseConnection(sw, d);

                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine(Indent(--d) + "}");
                sw.WriteLine();
            }
        }

        private string BuildUpserts()
        {
            string upsertTemplate = Assembly.GetExecutingAssembly().GetResourceTemplate("ContextUpsert.template");
            var result = new StringBuilder();
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                string upsert = upsertTemplate.Replace("%TableName%", table.name);

                string cmdto = _ddl.Settings.CommandTimeoutSpecified ? string.Format(Indent(5) + "command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout) : null;
                upsert = upsert.Replace("%CommandTimeout%", cmdto);

                upsert = upsert.Replace("%Parameters%", SqlParameters(table.Field.ToList(), false, 5));
                result.AppendLine(upsert);
            }
            return result.ToString();
        }

        private static void SqlParameters(IEnumerable<Field> fields, bool isParam, StreamWriter sw, int d)
        {
            foreach (var field in fields)
            {
                string paramValue = (!isParam) ? string.Format("det.{0}", field.title) : string.Format("{0}",field.name);
                if (field.ClrDataType == "string")
                    sw.WriteLine(Indent(d) + string.Format("SqlParameter {0}param = command.Parameters.Add(\"@{0}\", {1}, {2});", field.name, DataTypeConversion.GetSqlDbType(field), field.len));
                else
                    sw.WriteLine(Indent(d) + string.Format("SqlParameter {0}param = command.Parameters.Add(\"@{0}\", {1});", field.name, DataTypeConversion.GetSqlDbType(field)));
                if (field.nulls || field.ClrDataType == "string")
                {
                    if (DataTypeConversion.IsNullableType(field))
                    {
                        sw.WriteLine(Indent(d) + string.Format("if ({0}.HasValue) {1}param.Value = {0}.Value;", paramValue, field.name));
                    }
                    else
                    {
                        sw.WriteLine(Indent(d) + string.Format("if ({0}!=null) {1}param.Value = {0};", paramValue, field.name));
                    }
                    sw.WriteLine(Indent(d) + string.Format("else {0}param.Value = DBNull.Value;", field.name));
                }
                else
                {
                    sw.WriteLine(Indent(d) + string.Format("{0}param.Value = {1};", field.name, paramValue));
                }
            }
        }

        private static string SqlParameters(IEnumerable<Field> fields, bool isParam, int d)
        {
            var result = new StringBuilder();
            foreach (var field in fields)
            {
                string paramValue = (!isParam) ? string.Format("det.{0}", field.title) : string.Format("{0}", field.name);
                if (field.ClrDataType == "string")
                    result.AppendLine( Indent(d) + string.Format("SqlParameter {0}param = command.Parameters.Add(\"@{0}\", {1}, {2});", field.name, DataTypeConversion.GetSqlDbType(field), field.len));
                else
                    result.AppendLine(Indent(d) + string.Format("SqlParameter {0}param = command.Parameters.Add(\"@{0}\", {1});", field.name, DataTypeConversion.GetSqlDbType(field)));
                if (field.nulls || field.ClrDataType== "string")
                {
                    if (DataTypeConversion.IsNullableType(field))
                    {
                        result.AppendLine(Indent(d) + string.Format("if ({0}.HasValue) {1}param.Value = {0}.Value;", paramValue, field.name));
                    }
                    else
                    {
                        result.AppendLine(Indent(d) + string.Format("if ({0}!=null) {1}param.Value = {0};", paramValue, field.name));
                    }
                    result.AppendLine(Indent(d) + string.Format("else {0}param.Value = DBNull.Value;", field.name));
                }
                else
                {
                    result.AppendLine(Indent(d) + string.Format("{0}param.Value = {1};", field.name, paramValue));
                }
            }
            return result.ToString();
        }

        private void MaybeCloseConnection(StreamWriter sw, int d)
        {
            sw.WriteLine(Indent(d) + "if (!KeepAlive)");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "_conn.Close();");
            sw.WriteLine(Indent(--d) + "}");
        }
        private void MaybeOpenConnection(StreamWriter sw, int d)
        {
            sw.WriteLine(Indent(d) + "if (_conn.State != ConnectionState.Open)");
            sw.WriteLine(Indent(d++) + "{");
            sw.WriteLine(Indent(d) + "_conn.Open();");
            sw.WriteLine(Indent(--d) + "}");
        }

        private void BuildExecuteSqlMethod(StreamWriter sw, int ind)
        {
            sw.WriteLine(Indent(ind) + "public DataTable ExecuteSql(string sql)");
            sw.WriteLine(Indent(ind++) + "{");
            sw.WriteLine(Indent(ind) + "lock (_syncRoot)");
            sw.WriteLine(Indent(ind++) + "{");
            sw.WriteLine(Indent(ind) + "var result = new DataTable(\"ExecuteSql\");");
            sw.WriteLine(Indent(ind++) + "if (_conn.State != ConnectionState.Open)");
            sw.WriteLine(Indent(ind--) + "_conn.Open();");
            sw.WriteLine(Indent(ind) + "using (var command = new SqlCommand(sql, _conn))");
            sw.WriteLine(Indent(ind++) + "{");
            sw.WriteLine(Indent(ind++) + "if (_transactionActive)");
            sw.WriteLine(Indent(ind--) + "command.Transaction = _transaction;");
            sw.WriteLine(Indent(ind++) + "using (var reader = command.ExecuteReader())");
            sw.WriteLine(Indent(ind--) + "result.Load(reader);");
            sw.WriteLine(Indent(--ind) + "}");
            sw.WriteLine(Indent(ind++) + "if (!_keepAlive)");
            sw.WriteLine(Indent(ind--) + "_conn.Close();");

            sw.WriteLine(Indent(ind) + "return result;");
            sw.WriteLine(Indent(--ind) + "}");
            sw.WriteLine(Indent(--ind) + "}");
        }

        private void BuildDisposeMethod(StreamWriter sw, int ind)
        {
            sw.WriteLine(Indent(ind) + "public void Dispose()");
            sw.WriteLine(Indent(ind++) + "{");
            sw.WriteLine(Indent(ind) + "if (_transactionActive)");
            sw.WriteLine(Indent(ind++) + "{");
            sw.WriteLine(Indent(ind) + "RollbackTransaction();");
            sw.WriteLine(Indent(ind) + "_transactionActive = false;");
            sw.WriteLine(Indent(--ind) + "}");
            sw.WriteLine(Indent(ind) + "if (_conn != null)");
            sw.WriteLine(Indent(ind++) + "{");
            sw.WriteLine(Indent(ind++) + "if (_conn.State == ConnectionState.Open)");
            sw.WriteLine(Indent(ind--) + "_conn.Close();");
            sw.WriteLine(Indent(ind) + "_conn.Dispose();");
            sw.WriteLine(Indent(ind) + "_conn = null;");
            sw.WriteLine(Indent(--ind) + "}");
            sw.WriteLine(Indent(--ind) + "}");
        }

        public void BuildDataContracts()
        {
            int d = 0;
            string tmpfile = Path.GetTempFileName();
            using (var sw = new StreamWriter(tmpfile))
            {
                sw.WriteLine("/*");
                sw.WriteLine("This file was generated by some tool.");
                sw.WriteLine("SqlData.exe");
                sw.WriteLine("*/");
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Runtime.Serialization;");
                sw.WriteLine(string.Format("namespace {0}", _ddl.Settings.DataContractOutput.clrnamespace));
                sw.WriteLine(Indent(d++) + "{");
                // enums
                foreach (var table in _ddl.Definition.Schema.Table)
                {
                    var enumFields = table.Field.Where(x => !string.IsNullOrEmpty(x.EnumName));
                    foreach (var f in enumFields)
                    {
                        //if (f.datatype == FieldDataType.@byte || f.datatype == FieldDataType.int16 || f.datatype == FieldDataType.int32 || f.datatype == FieldDataType.int64)
                        if (f.ClrDataType == "byte" || f.ClrDataType == "Int16" || f.ClrDataType == "int" || f.ClrDataType == "long")
                        {
                            f.EnumName = string.Format("{0}{1}", table.name, f.title);
                            sw.Write(Indent(d) + "public enum " + f.EnumName + " : " + f.UnderlyingEnumType + " { ");
                            foreach (var e in f.Enum)
                            {
                                string enumVal;
                                if (!string.IsNullOrEmpty(e.value))
                                    enumVal = e.value;
                                else
                                    enumVal = (Array.IndexOf(f.Enum, e) + 1).ToString();
                                sw.Write(string.Format("{0} = {1}", e.desc, enumVal));
                                if (Array.IndexOf(f.Enum, e) != f.Enum.Count() - 1)
                                    sw.Write(", ");
                            }
                            sw.WriteLine(" }");
                        }
                        else
                            throw new Exception("Enum must be of type [byte,int16,int32,long]");
                    }
                }
                foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.datacontract))
                {
                    sw.WriteLine(Indent(d) + string.Format(@"[DataContract(Namespace = ""{0}"", Name = ""{1}"")]",_ddl.Settings.DataContractOutput.urinamespace, table.name));
                    sw.WriteLine(string.Format(Indent(d) + "public partial class {0}", table.name));
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(string.Format(Indent(d) + "public {0}() ", table.name) + "{ }");
                    int count = 0;
                    foreach (var f in table.Field)
                    {
                        sw.WriteLine(Indent(d) + string.Format("[DataMember(Name = \"{0}\", IsRequired = true, Order = {1})]",f.title,count));
                        sw.WriteLine(string.Format(Indent(d) + "public {0} {1} ", f.ClrDataType, f.title) + "{ get; set; }");
                        count++;
                    }
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine();
                }
                sw.WriteLine("}");
            }
            Move(_ddl.Settings.DataContractOutput.pathname, tmpfile);
        }

        public void BuildAllEntities()
        {
            BuildEntites(_ddl.Settings.EntitiesOutput.pathname, _ddl.Settings.EntitiesOutput.clrnamespace);
            if (_ddl.Settings.BusinessOutput != null)
                BuildEntites(_ddl.Settings.BusinessOutput.pathname, _ddl.Settings.BusinessOutput.clrnamespace);
        }

        private void BuildEntites(string path, string clrnamespace)
        {
            int d = 0;
            string tmpfile = Path.GetTempFileName();
            using (var sw = new StreamWriter(tmpfile))
            {
                sw.WriteLine("/*");
                sw.WriteLine("This file was generated by some tool.");
                sw.WriteLine("SqlData.exe");
                sw.WriteLine("*/");
                sw.WriteLine("using System;");
                sw.WriteLine(string.Format("namespace {0}", clrnamespace));
                sw.WriteLine(Indent(d++) + "{");
                // enums
                foreach (var table in _ddl.Definition.Schema.Table)
                {
                    var enumFields = table.Field.Where(x => !string.IsNullOrEmpty(x.EnumName));
                    foreach (var f in enumFields)
                    {
                        //if (f.datatype == FieldDataType.@byte || f.datatype == FieldDataType.int16 || f.datatype == FieldDataType.int32 || f.datatype == FieldDataType.int64)
                        if (f.ClrDataType == "byte" || f.ClrDataType == "Int16" || f.ClrDataType == "int" || f.ClrDataType == "long")
                        {
                            f.EnumName = string.Format("{0}{1}", table.name, f.title);
                            sw.Write(Indent(d) + "public enum " + f.EnumName + " : " + f.UnderlyingEnumType + " { ");
                            foreach (var e in f.Enum)
                            {
                                string enumVal;
                                if (!string.IsNullOrEmpty(e.value))
                                    enumVal = e.value;
                                else
                                    enumVal = (Array.IndexOf(f.Enum, e) + 1).ToString();
                                sw.Write(string.Format("{0} = {1}", e.desc, enumVal));
                                if (Array.IndexOf(f.Enum, e) != f.Enum.Count() - 1)
                                    sw.Write(", ");
                            }
                            sw.WriteLine(" }");
                        }
                        else
                            throw new Exception("Enum must be of type [byte,int16,int32,long]");
                    }
                }
                // entities
                foreach (var table in _ddl.Definition.Schema.Table)
                {
                    sw.WriteLine(string.Format(Indent(d) + "public partial class {0}", table.name));
                    sw.WriteLine(Indent(d++) + "{");
                    sw.WriteLine(string.Format(Indent(d) + "public {0}() ", table.name) + "{ }");
                    foreach (var f in table.Field)
                    {
                        //if (f.Enum != null && f.Enum.Count() > 0)
                        //    sw.WriteLine(string.Format(Indent(d) + "public {0} {1} ", table.name + f.title, f.title) + "{ get; set; }");
                        //else
                            sw.WriteLine(string.Format(Indent(d) + "public {0} {1} ", f.ClrDataType, f.title) + "{ get; set; }");
                    }
                    sw.WriteLine(Indent(--d) + "}");
                    sw.WriteLine();
                }
                if (_ddl.Definition.Procedures != null)
                {
                    foreach (var proc in _ddl.Definition.Procedures)
                    {
                        if (proc.Output != null && proc.UseFields.Count > 0)
                        {
                            string returnType = proc.name + "Output";
                            sw.WriteLine(string.Format(Indent(d) + "public class {0}", returnType));
                            sw.WriteLine(Indent(d++) + "{");
                            sw.WriteLine(string.Format(Indent(d) + "public {0}() ", returnType) + "{ }");
                            foreach (var f in proc.UseFields)
                            {
                                sw.WriteLine(string.Format(Indent(d) + "public {0} {1} ", f.ClrDataType, f.title) + "{ get; set; }");
                            }
                            sw.WriteLine(Indent(--d) + "}");
                            sw.WriteLine();
                        }
                    }
                }
                sw.WriteLine(Indent(--d) + "}");
                sw.Flush();
            }
            Move(path, tmpfile);
            //Move(Path.GetDirectoryName(_ddl.Settings.ContextOutput.pathname) + "\\" + _ddl.Settings.Database + "Entities.cs", tmpfile);
        }



        public static void Move(string path, string tmpfile)
        {
            if (File.Exists(path))
                File.Delete(path);
            File.Move(tmpfile, path);
        }

        public static string Indent(int num)
        {
            if (num == 0)
                return string.Empty;
            return new string('\t', num);
        }
    }
}